<?php
class ModelCommunityPost extends Model{
    public function getForumInfo($getData){
        $encrypt_data=md5($getData['fid']);

        $query=$this->sql_cache->get('post.foruminfo.' . $encrypt_data . '.' . (int)$this->config->get('config_language_id'));

        if(!$query){
            $query=$this->db->query("SELECT fn.forum_name, f.forum_type, f.forum_enabled, f.topic_category_enabled FROM " . DB_PREFIX . "community_forum AS f LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE f.forum_id=" . $getData['fid'] . " LIMIT 0,1");

            $this->sql_cache->set('post.foruminfo.' . $encrypt_data . '.' . (int)$this->config->get('config_language_id'),$query);
        }

        return $query->row;
    }

    public function getForumTopicCats($getData){
        $encrypt_data=md5($getData['fid']);

        $query=$this->sql_cache->get('post.topiccats.' . $encrypt_data . '.' . (int)$this->config->get('config_language_id'));
        if(!$query){
            $query=$this->db->query("SELECT t.topic_category_id, tc.topic_category_title FROM " . DB_PREFIX . "community_topic_category AS t LEFT JOIN " . DB_PREFIX . "community_topic_category_title AS tc ON (tc.topic_category_id=t.topic_category_id AND tc.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE t.forum_id='" . $getData['fid'] . "' ORDER BY t.sort_order_id ASC");

            $this->sql_cache->set('post.topiccats.' . $encrypt_data . '.' . (int)$this->config->get('config_language_id'),$query);
        }

        return $query->rows;
    }

    public function editGetTopicInfo($getData){
        $query=$this->db->query("SELECT t.topic_title, t.topic_featured, t.topic_type, t.topic_time_limit, t.topic_status, t.topic_category_id, t.topic_first_post_id, t.poll_title, t.poll_max_options, t.poll_length, n.topic_id FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_topic_notify AS n ON (n.user_id='" . $this->customer->getForumUserId() . "' AND t.topic_id=n.topic_id) WHERE t.topic_id=" . $getData['tid'] . " LIMIT 0,1");
        return $query->row;
    }

    public function replyGetTopicInfo($getData){
        $encrypt_data=md5($getData['tid']);

        $result=$this->sql_cache->get('post.replytopicinfo.' . $encrypt_data);

        if(!$result){
            $result=$this->db->query("SELECT topic_id AS tid, topic_title, topic_featured, topic_type, topic_first_post_id, topic_time_limit, topic_status FROM " . DB_PREFIX . "community_topics WHERE topic_id=" . (int)$getData['tid'] . " LIMIT 0,1");

            $this->sql_cache->set('post.replytopicinfo.' . $encrypt_data,$result,TRUE,(60*60*24));
        }

        $notify_result=$this->db->query("SELECT topic_id FROM " . DB_PREFIX . "community_topic_notify WHERE topic_id='" . (int)$result->row['tid'] . "' AND user_id='" . (int)$this->customer->getForumUserId() . "'");

        $query=$result->row;

        if(!empty($notify_result->row)){
            $query['topic_id']=$notify_result->row['topic_id'];
        }

        return $query;
    }

    public function getPollOptions($getData){
        $query=$this->db->query("SELECT poll_option_text FROM " . DB_PREFIX . "community_poll_options WHERE topic_id='" . (int)$getData['tid'] . "' AND topic_deleted='0' ORDER BY poll_option_id ASC");

        return $query->rows;
    }

    public function getPostInfo($getData){
        $query=$this->db->query("SELECT p.poster_id, p.post_subject, p.post_text, p.enable_bbcode, p.enable_smilies, p.enable_magic_url, p.enable_sig, p.post_edit_reason, p.post_edit_locked, n.topic_id FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topic_notify AS n ON (p.poster_id=n.user_id AND p.topic_id=n.topic_id) WHERE post_id=" . $getData['pid'] . " LIMIT 0,1");
        return $query->row;
    }

    public function addTopic($getData,$postData,$postingNotApproval){
        if($postingNotApproval){
            $approved=0;
        }else{
            $approved=1;
        }

        $topic_featured=isset($postData['feature_topic']) ? 1 : 0;
        $topic_polled=!empty($postData['poll-question']) ? 1 : 0;
        $topic_status=isset($postData['lock_topic']) ? 1 : 0;
        $topic_type=isset($postData['set_topic_as']) ? $postData['set_topic_as'] : 0;

        if($topic_type==0){
            $topic_time_limit=0;
        }else{
            $topic_time_limit=isset($postData['stick_topic_for']) && $postData['stick_topic_for'] ? ($postData['stick_topic_for']*60*60*24+time()) : 0;
        }

        $post_edit_locked=isset($postData['lock_edit']) ? 1 : 0;

        //add topics and posts
        $this->db->query("INSERT INTO " . DB_PREFIX . "community_topics SET forum_id='" . (int)$getData['fid'] . "', topic_category_id='" . (int)(isset($postData['topic-category']) ? $postData['topic-category'] : '') . "', topic_featured='" . (int)$topic_featured . "', topic_polled='" . (int)$topic_polled . "', topic_approved='" . (int)$approved . "', topic_status='" . (int)$topic_status . "', topic_type='" . (int)$topic_type . "', topic_time_limit='" . (int)$topic_time_limit . "', topic_title='" . $this->db->escape($postData['subject']) . "', topic_poster_id='" . (int)$this->customer->getForumUserId() . "', topic_time='" . (int)time() . "', topic_first_poster_name='" . $this->db->escape($this->customer->getForumUserName()) . "', topic_last_poster_id='" . (int)$this->customer->getForumUserId() . "', topic_last_poster_name='" . $this->db->escape($this->customer->getForumUserName()) . "', topic_last_post_subject='" . $this->db->escape($postData['subject']) . "', topic_last_post_time='" . (int)time() . "', topic_last_view_time='" . (int)time() . "', poll_title='" . $this->db->escape(isset($postData['poll-question']) ? $postData['poll-question'] : '') . "', poll_start='" . (int)time() . "', poll_length='" . (int)(isset($postData['poll-run-day']) ? ($postData['poll-run-day']*60*60*24) : '') . "', poll_max_options='" . (int)(isset($postData['poll-option-per-user']) ? $postData['poll-option-per-user'] : 0) . "'");

        $topicId=$this->db->getLastId();

        //add poll options
        if(!empty($postData['poll-question'])){
            $poll_option_text=nl2br($postData['poll-option']);
            $temp01_poll_options=explode('<br />',$poll_option_text);

            $temp02_poll_options=array();

            foreach($temp01_poll_options as $temp01_poll_option){
                $temp01_poll_option=trim(trim($temp01_poll_option),'<br />');//remove things like \n,\t

                if(!empty($temp01_poll_option)){
                    array_push($temp02_poll_options,$temp01_poll_option);
                }
            }

            //remove duplicated entities
            $poll_options=array_unique($temp02_poll_options);

            $poll_option_id=0;

            foreach($poll_options as $poll_option){
                $this->db->query("INSERT INTO " . DB_PREFIX . "community_poll_options SET topic_id='" . (int)$topicId . "', poll_option_id='" . (int)$poll_option_id . "', poll_option_text='" . $this->db->escape($poll_option) . "', poll_option_total='0'");

                $poll_option_id++;
            }
        }

        $enableBbcode=isset($postData['disable_bbcode']) ? 0 : 1;
        $enableSmilies=isset($postData['disable_smilies']) ? 0 : 1;
        $enableMagicUrl=isset($postData['not_parse_urls']) ? 0 : 1;
        $enableSig=isset($postData['attach_signature']) ? 1 : 0;
        $notifyMe=isset($postData['notify_me']) ? true : false;

        $this->db->query("INSERT INTO " . DB_PREFIX . "community_posts SET topic_id='" . (int)$topicId . "', forum_id='" . (int)$getData['fid'] . "', poster_id='" . (int)$this->customer->getForumUserId() . "', poster_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', post_time='" . (int)time() . "', post_approved='" . $approved . "', enable_bbcode='" . (int)$enableBbcode . "', enable_smilies='" . (int)$enableSmilies . "', enable_magic_url='" . (int)$enableMagicUrl . "', enable_sig='" . (int)$enableSig . "', post_username='" . $this->db->escape($this->customer->getForumUserName()) . "', post_subject='" . $this->db->escape($postData['subject']) . "', post_text='" . $this->db->escape($postData['content']) . "', post_edit_locked='" . (int)$post_edit_locked . "'");

        $postId=$this->db->getLastId();

        $this->db->query("UPDATE "  . DB_PREFIX . "community_topics SET topic_first_post_id='" . (int)$postId . "', topic_last_post_id='" . (int)$postId . "' WHERE topic_id='" . (int)$topicId . "'");

        //topic subscription
        if($notifyMe){
            $this->db->query("INSERT INTO " . DB_PREFIX . "community_topic_notify SET user_id='" . (int)$this->customer->getForumUserId() . "', topic_id='" . (int)$topicId . "'");
        }

        if($approved==1){
            //update user's num of topics and posts
            $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_topics=user_topics+1, user_posts=user_posts+1, user_lastpost_time='" . (int)time() . "' WHERE user_id='" . (int)$this->customer->getForumUserId() . "'");

            //update relative field values of forum
            $this->db->query("UPDATE " . DB_PREFIX . "community_forum SET forum_posts=forum_posts+1, forum_topics=forum_topics+1, forum_last_post_id='" . $postId . "', forum_last_poster_id='" . (int)$this->customer->getForumUserId() . "', forum_last_post_subject='" . $this->db->escape($postData['subject']) . "', forum_last_post_time='" . (int)time() . "', forum_last_poster_name='" . $this->db->escape($this->customer->getForumUserName()) . "' WHERE forum_id='" . (int)$getData['fid'] . "'");

            //update forum statics data
            $this->db->query("UPDATE " . DB_PREFIX . "setting SET `value`=`value`+1 WHERE `key`='total_topics'");
            $this->db->query("UPDATE " . DB_PREFIX . "setting SET `value`=`value`+1 WHERE `key`='total_posts'");

            $this->sendForumNewTopicNotification($getData,$postData['subject'],$topicId);
        }

        $this->UpdateUserRank();

        //clear sql cache
        $encrypt_data=md5($getData['fid']);
        $this->sql_cache->delete('viewforum.topictotal.' . $encrypt_data);
        $this->sql_cache->delete('viewforum.topics.' . $encrypt_data);
        $this->sql_cache->delete('community.foruminfo.');

        if($topic_type=2){
            $this->sql_cache->delete('viewforum.announcementtopics.' . $encrypt_data);
        }

        if($topic_type=3){
            $this->sql_cache->delete('viewforum.announcementtopics.');
        }

        return $topicId;
    }

    public function editPost($getData,$postData,$postingNotApproval){
        if($postingNotApproval){
            $approved=0;
        }else{
            $approved=1;
        }

        $topic_featured=isset($postData['feature_topic']) ? 1 : 0;
        $topic_status=isset($postData['lock_topic']) ? 1 : 0;
        $topic_type=isset($postData['set_topic_as']) ? $postData['set_topic_as'] : 0;

        if($topic_type==0){
            $topic_time_limit=0;
        }else{
            $topic_time_limit=isset($postData['stick_topic_for']) && $postData['stick_topic_for'] ? ($postData['stick_topic_for']*60*60*24+time()) : 0;
        }

        $post_edit_locked=isset($postData['lock_edit']) ? 1 : 0;

        $enableBbcode=isset($postData['disable_bbcode']) ? 0 : 1;
        $enableSmilies=isset($postData['disable_smilies']) ? 0 : 1;
        $enableMagicUrl=isset($postData['not_parse_urls']) ? 0 : 1;
        $enableSig=isset($postData['attach_signature']) ? 1 : 0;
        $notifyMe=isset($postData['notify_me']) ? true : false;

        //Update post and if post is a topic, update topic
        $sql="UPDATE " . DB_PREFIX . "community_posts SET post_approved='" . (int)$approved . "', post_edit_locked='" . (int)$post_edit_locked . "', enable_bbcode='" . (int)$enableBbcode . "', enable_smilies='" . (int)$enableSmilies . "', enable_magic_url='" . (int)$enableMagicUrl . "', enable_sig='" . (int)$enableSig . "', post_subject='" . $this->db->escape($postData['subject']) . "', post_text='" . $this->db->escape($postData['content']) . "'";

        if(!empty($postData['post_edit_reason'])){
            $sql.=", post_edit_time='" . (int)time() . "', post_edit_reason='" . $this->db->escape($postData['post_edit_reason']) . "', post_edit_user='" . (int)$this->customer->getForumUserId() . "', post_edit_count=post_edit_count+1";

            //add moderator log
            if($this->customer->getForumUserType()<=3){
                $postQuery=$this->db->query("SELECT p.post_id, p.post_subject, p.poster_id, p.post_username, t.topic_id, t.topic_title, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE p.post_id='" . (int)$getData['pid'] . "' LIMIT 0,1");

                $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='post_edit', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$postQuery->row['poster_id'] . "', forum_id='" . (int)$postQuery->row['forum_id'] . "', topic_id='" . (int)$postQuery->row['topic_id'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($postQuery->row['post_id'],$this->db->escape($postQuery->row['post_subject']),$postQuery->row['poster_id'],$this->db->escape($postQuery->row['post_username']),$this->db->escape($postQuery->row['topic_title']),$this->db->escape($postQuery->row['forum_name']))) . "'");
            }
        }

        $sql.=" WHERE post_id='" . (int)$getData['pid'] . "'";

        $this->db->query($sql);

        if($postData['topic-first-post-id']==$getData['pid']){//if it is a topic
            $this->db->query("UPDATE " . DB_PREFIX . "community_topics SET topic_category_id='" . (int)(isset($postData['topic-category']) ? $postData['topic-category'] : 0) . "', topic_approved='" . (int)$approved . "', topic_featured='" . (int)$topic_featured . "', topic_status='" . (int)$topic_status . "', topic_type='" . (int)$topic_type . "', topic_time_limit='" . (int)$topic_time_limit . "', topic_title='" . $postData['subject'] . "', poll_title='" . $this->db->escape($postData['poll-question']) . "', poll_start='" . (int)time() . "', poll_length='" . (int)($postData['poll-run-day']*60*60*24) . "', poll_max_options='" . (int)$postData['poll-option-per-user'] . "' WHERE topic_id='" . (int)$getData['tid'] . "'");

            //add forum post for featured post
            if($topic_featured){
                $customer_query=$this->db->query("SELECT u.user_id, c.master_slave FROM " . DB_PREFIX . "community_users AS u LEFT JOIN " . DB_PREFIX . "customer AS c ON (c.customer_id=u.customer_id) WHERE u.user_id=(SELECT topic_poster_id FROM " . DB_PREFIX . "community_topics WHERE topic_id='" . (int)$getData['tid'] . "' LIMIT 0,1) LIMIT 0,1");

                if($customer_query->row['master_slave']!='sl'){
                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_forum_point_history SET user_id='" . (int)$customer_query->row['user_id'] . "', type='4', description='" . $this->db->escape('Featured Post Awarded Forum Points') . "', points='" . (int)$this->config->get('featured_topic_awarded_forum_points') . "', add_time='" . time() . "'");

                    $this->db->query("UPDATE " . DB_PREFIX . "community_users SET forum_points=forum_points+" . (int)$this->config->get('featured_topic_awarded_forum_points') . " WHERE user_id='" . (int)$customer_query->row['user_id'] . "'");
                }
            }
        }

        //update poll options
        if(!empty($postData['poll-question'])){
            $poll_option_text=nl2br($postData['poll-option']);
            $temp01_poll_options=explode('<br />',$poll_option_text);

            $temp02_poll_options=array();

            foreach($temp01_poll_options as $temp01_poll_option){
                $temp01_poll_option=trim(trim($temp01_poll_option),'<br />');//remove things like \n,\t

                if(!empty($temp01_poll_option)){
                    array_push($temp02_poll_options,$temp01_poll_option);
                }
            }

            //remove duplicated entities
            $poll_options=array_unique($temp02_poll_options);

            $poll_option_id=0;

            foreach($poll_options as $poll_option){
                $this->db->query("UPDATE " . DB_PREFIX . "community_poll_options SET poll_option_text='" . $this->db->escape($poll_option) . "' WHERE poll_option_id='" . (int)$poll_option_id . "' AND topic_id='" . (int)$getData['tid'] . "'");

                $poll_option_id++;
            }
        }

        //topic subscription
        $query=$this->db->query("SELECT poster_id FROM " . DB_PREFIX . "community_posts WHERE post_id='" . $getData['pid'] . "'");
        $poster_id=$query->row['poster_id'];
        if($this->getTopicNotify($getData,$poster_id)){
            if(!$notifyMe){
                $this->db->query("DELETE FROM " . DB_PREFIX . "community_topic_notify WHERE user_id='" . (int)$poster_id . "' AND topic_id='" . (int)$getData['tid'] . "'");
            }
        }else{
            if($notifyMe){
                $this->db->query("INSERT INTO " . DB_PREFIX . "community_topic_notify SET user_id='" . (int)$poster_id . "', topic_id='" . (int)$getData['tid'] . "', topic_notified='0'");
            }
        }

        if($approved==1){
            //update relative field values of forum
            $this->db->query("UPDATE " . DB_PREFIX . "community_forum SET forum_last_post_subject='" . $this->db->escape($postData['subject']) . "' WHERE forum_id='" . (int)$getData['fid'] . "'");
        }

        //add moderator log
        if($this->customer->getForumUserType()<=3){
            $postQuery=$this->db->query("SELECT p.post_id, p.post_subject, p.poster_id, p.post_username, t.topic_id, t.topic_title, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE p.post_id='" . (int)$getData['pid'] . "' LIMIT 0,1");

            if($post_edit_locked){
                $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='post_lock', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$postQuery->row['poster_id'] . "', forum_id='" . (int)$postQuery->row['forum_id'] . "', topic_id='" . (int)$postQuery->row['topic_id'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($postQuery->row['post_id'],$this->db->escape($postQuery->row['post_subject']),$postQuery->row['poster_id'],$this->db->escape($postQuery->row['post_username']),$this->db->escape($postQuery->row['topic_title']),$this->db->escape($postQuery->row['forum_name']))) . "'");
            }else{
                $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='post_unlock', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$postQuery->row['poster_id'] . "', forum_id='" . (int)$postQuery->row['forum_id'] . "', topic_id='" . (int)$postQuery->row['topic_id'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($postQuery->row['post_id'],$this->db->escape($postQuery->row['post_subject']),$postQuery->row['poster_id'],$this->db->escape($postQuery->row['post_username']),$this->db->escape($postQuery->row['topic_title']),$this->db->escape($postQuery->row['forum_name']))) . "'");
            }
        }

        //clear sql cache
        $encrypt_data=md5($getData['tid']);
        $this->sql_cache->delete('viewtopic.posts.' . $encrypt_data);
        $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));

        if($postData['topic-first-post-id']==$getData['pid']){
            $this->sql_cache->delete('viewtopic.topicinfo.' . $encrypt_data);
            $this->sql_cache->delete('post.replytopicinfo.' . $encrypt_data);
        }

        if(!empty($postData['poll-question'])){
            $this->sql_cache->delete('viewtopic.polloptions.' . $encrypt_data);
        }

        if((($postData['topic-type']==0 || $postData['topic-type']==1) && $topic_type==2 ) || ($postData['topic-type']==2  && ($topic_type==0 || $topic_type==1)) || ($postData['topic-type']==2 && $topic_type==2)){
            $this->sql_cache->delete('viewforum.announcementtopics.' . md5($getData['fid']));
        }

        if((($postData['topic-type']==0 || $postData['topic-type']==1) && $topic_type==3 ) || ($postData['topic-type']==3  && ($topic_type==0 || $topic_type==1)) || ($postData['topic-type']==3 && $topic_type==3)){
            $this->sql_cache->delete('viewforum.announcementtopics.');
        }
    }

    public function addReply($getData,$postData,$postingNotApproval){
        if($postingNotApproval){
            $approved=0;
        }else{
            $approved=1;
        }

        $enableBbcode=isset($postData['disable_bbcode']) ? 0 : 1;
        $enableSmilies=isset($postData['disable_smilies']) ? 0 : 1;
        $enableMagicUrl=isset($postData['not_parse_urls']) ? 0 : 1;
        $enableSig=isset($postData['attach_signature']) ? 1 : 0;
        $notifyMe=isset($postData['notify_me']) ? true : false;

        $post_edit_locked=isset($postData['lock_edit']) ? 1 : 0;

        //add posts and update topic's post number
        $this->db->query("INSERT INTO " . DB_PREFIX . "community_posts SET topic_id='" . (int)$getData['tid'] . "', forum_id='" . (int)$getData['fid'] . "', poster_id='" . (int)$this->customer->getForumUserId() . "', poster_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', post_time='" . (int)time() . "', post_approved='" . (int)$approved . "', enable_bbcode='" . (int)$enableBbcode . "', enable_smilies='" . (int)$enableSmilies . "', enable_magic_url='" . (int)$enableMagicUrl . "', enable_sig='" . (int)$enableSig . "', post_username='" . $this->db->escape($this->customer->getForumUserName()) . "', post_subject='" . $this->db->escape($postData['subject']) . "', post_text='" . $this->db->escape($postData['content']) .  "', post_edit_locked='" . (int)$post_edit_locked . "'");

        $postId=$this->db->getLastId();

        if($approved==1){
            $this->db->query("UPDATE " . DB_PREFIX . "community_topics SET topic_replies=topic_replies+1, topic_last_post_id='" . (int)$postId . "', topic_last_poster_id='" . (int)$this->customer->getForumUserId() . "', topic_last_poster_name='" . $this->db->escape($this->customer->getForumUserName()) . "', topic_last_post_subject='" . $this->db->escape($postData['subject']) . "', topic_last_post_time='" . (int)time() . "', topic_last_view_time='" . (int)time() . "' WHERE topic_id='" . (int)$getData['tid'] . "'");

            //update user's num of posts
            $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_posts=user_posts+1, user_lastpost_time='" . (int)time() . "' WHERE user_id='" . (int)$this->customer->getForumUserId() . "'");

            //update relative field values of forum
            $this->db->query("UPDATE " . DB_PREFIX . "community_forum SET forum_posts=forum_posts+1, forum_last_post_id='" . $postId . "', forum_last_poster_id='" . (int)$this->customer->getForumUserId() . "', forum_last_post_subject='" . $this->db->escape($postData['subject']) . "', forum_last_post_time='" . (int)time() . "', forum_last_poster_name='" . $this->db->escape($this->customer->getForumUserName()) . "' WHERE forum_id='" . (int)$getData['fid'] . "'");

            //update forum statics data
            $this->db->query("UPDATE " . DB_PREFIX . "setting SET `value`=`value`+1 WHERE `key`='total_posts'");

            $this->sendTopicNotification($getData,$postId);
            $this->sendForumNewPostNotification($getData,$postId);
        }

        //topic subscription
        if($this->getTopicNotify($getData,$this->customer->getForumUserId())){
            if(!$notifyMe){
                $this->db->query("DELETE FROM " . DB_PREFIX . "community_topic_notify WHERE user_id='" . (int)$this->customer->getForumUserId() . "' AND topic_id='" . (int)$getData['tid'] . "'");
            }
        }else{
            if($notifyMe){
                $this->db->query("INSERT INTO " . DB_PREFIX . "community_topic_notify SET user_id='" . (int)$this->customer->getForumUserId() . "', topic_id='" . (int)$getData['tid'] . "', topic_notified='0'");
            }
        }

        $this->UpdateUserRank();

        //clear sql cache
        $encrypt_data=md5($getData['tid']);
        $this->sql_cache->delete('viewtopic.posttotal.' . $encrypt_data);
        $this->sql_cache->delete('viewtopic.postids.' . $encrypt_data);
        $this->sql_cache->delete('viewtopic.posts.' . $encrypt_data);
        $this->sql_cache->delete('viewtopic.topicinfo.' . $encrypt_data);
        $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
        $this->sql_cache->delete('community.foruminfo.');

        if($postData['topic-type']=2){
            $this->sql_cache->delete('viewforum.announcementtopics.' . md5($getData['fid']));
        }

        if($postData['topic-type']=3){
            $this->sql_cache->delete('viewforum.announcementtopics.');
        }
    }

    //draft
    public function saveDraft($getData,$postData){
        if(isset($getData['tid'])){
            $this->db->query("INSERT INTO " . DB_PREFIX . "community_drafts SET user_id='" . (int)$this->customer->getForumUserId() . "', topic_id='" . (int)$getData['tid'] . "', forum_id='" . (int)$getData['fid'] . "', save_time='" . (int)time() . "', draft_subject='" . $this->db->escape($postData['subject']) . "', draft_contents='" . $this->db->escape($postData['content']) . "'");
        }else{
            $this->db->query("INSERT INTO " . DB_PREFIX . "community_drafts SET user_id='" . (int)$this->customer->getForumUserId() . "', topic_id='0', forum_id='" . (int)$getData['fid'] . "', save_time='" . (int)time() . "', draft_subject='" . $this->db->escape($postData['subject']) . "', draft_contents='" . $this->db->escape($postData['content']) . "'");
        }
    }

    public  function getTotalDrafts($getData){
        if(isset($getData['tid'])){
            $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_drafts WHERE user_id='" . (int)$this->customer->getForumUserId() .  "' AND topic_id='" . (int)$getData['tid'] . "' AND forum_id='" . (int)$getData['fid'] . "'");

            return $query->row['total'];
        }else{
            $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_drafts WHERE user_id='" . (int)$this->customer->getForumUserId() . "' AND forum_id='" . (int)$getData['fid'] . "'");

            return $query->row['total'];
        }
    }

    public function  loadDrafts($getData){
        if(isset($getData['tid'])){
            $query=$this->db->query("SELECT d.draft_id, d.topic_id, d.save_time, d.draft_subject, d.draft_contents, t.topic_title FROM " . DB_PREFIX . "community_drafts AS d LEFT JOIN " . DB_PREFIX . "community_topics AS t ON(d.topic_id=t.topic_id) WHERE d.user_id='" . (int)$this->customer->getForumUserId() . "' AND d.forum_id='" . (int)$getData['fid'] . "' AND d.topic_id='" . $getData['tid'] . "' ORDER BY d.save_time DESC");

            return $query->rows;
        }else{
            $query=$this->db->query("SELECT d.draft_id, d.forum_id ,d.save_time, d.draft_subject, d.draft_contents, fn.forum_name FROM " . DB_PREFIX . "community_drafts AS d LEFT JOIN " . DB_PREFIX . "community_forum AS f ON(d.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE d.user_id='" . (int)$this->customer->getForumUserId() . "' AND d.forum_id='" . (int)$getData['fid'] . "' AND d.topic_id='0' ORDER BY d.save_time DESC");

            return $query->rows;
        }
    }

    public function loadDraft($getData){
            $query=$this->db->query("SELECT draft_subject, draft_contents FROM " . DB_PREFIX . "community_drafts WHERE draft_id='" . $getData['did'] . "' LIMIT 1");
            return $query->row;
    }

    public function deleteDraft($getData){
        $this->db->query("DELETE FROM " . DB_PREFIX . "community_drafts WHERE draft_id='" . $getData['did'] . "' LIMIT 1");
    }

    private function getTopicNotify($getData,$poster_id){
        $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_topic_notify WHERE user_id='" . (int)$poster_id . "' AND topic_id='" . $getData['tid'] . "'");
        return $query->row['total'];
    }

    private function sendTopicNotification($getData,$postId){
        $query=$this->db->query("SELECT n.user_id, u.user_email FROM " . DB_PREFIX . "community_topic_notify AS n LEFT JOIN " . DB_PREFIX . "community_users AS u ON (n.user_id=u.user_id) WHERE n.topic_id='" . (int)$getData['tid'] . "' AND n.topic_deleted=0 AND n.topic_notified='0' AND (n.user_id>'" . (int)$this->customer->getForumUserId() . "' OR n.user_id<'" . (int)$this->customer->getForumUserId() . "')");

        $topicInfo=$this->replyGetTopicInfo($getData);

        foreach($query->rows as $result){
           if(isset($result['user_id'])){
               //send mails
               $this->language->load('community/post');

               $subject = sprintf($this->language->get('text_topic_reply_subject'), $topicInfo['topic_title']);

               $message = sprintf($this->language->get('text_topic_reply_content'), $topicInfo['topic_title'], $this->config->get('config_name')) . "\n\n";
               $message .= $this->language->get('text_topic_reply_view_reply') . "\n";
               $message .= $this->url->link('community/viewtopic', 'fid=' . $getData['fid'] . '&tid=' . $getData['tid'] . '&pid=' . $postId . '#' . $postId, 'SSL') . "\n\n";
               $message .= $this->language->get('text_topic_reply_view_topic') . "\n";
               $message .= $this->url->link('community/viewtopic', 'fid=' . $getData['fid'] . '&tid=' . $getData['tid'], 'SSL') . "\n\n";
               $message .= $this->language->get('text_topic_reply_view_forum') . "\n";
               $message .=$this->url->link('community/viewforum', 'fid=' . $getData['fid'] , 'SSL') . "\n\n";
               $message .= $this->language->get('text_topic_reply_thanks') . "\n";
               $message .= $this->config->get('config_name') . "\40" . $this->language->get('text_topic_reply_team');

               $mail = new Mail();
               $mail->protocol = $this->config->get('config_mail_protocol');
               $mail->parameter = $this->config->get('config_mail_parameter');
               $mail->hostname = $this->config->get('config_smtp_host');
               $mail->username = $this->config->get('config_smtp_username');
               $mail->password = $this->config->get('config_smtp_password');
               $mail->port = $this->config->get('config_smtp_port');
               $mail->timeout = $this->config->get('config_smtp_timeout');
               $mail->setTo($result['user_email']);
               $mail->setFrom($this->config->get('config_community_email'));//to-do: ad communit_email in the admin end
               $mail->setSender($this->config->get('config_name') . "\40" . $this->language->get('text_topic_reply_team'));
               $mail->setSubject(html_entity_decode($subject, ENT_QUOTES, 'UTF-8'));
               $mail->setText(html_entity_decode($message, ENT_QUOTES, 'UTF-8'));
               $mail->send();

               // update community_topic_notify record
               $this->db->query("UPDATE " . DB_PREFIX . "community_topic_notify SET topic_notified='1' WHERE topic_id='" . (int)$getData['tid'] . "' AND user_id='" . (int)$result['user_id'] . "'");
           }
        }
    }

    private function sendForumNewTopicNotification($getData,$topicTitle,$topicId){
        $query=$this->db->query("SELECT fn.user_id, u.user_email FROM " . DB_PREFIX . "community_forum_notify AS fn LEFT JOIN " . DB_PREFIX . "community_users AS u ON (fn.user_id=u.user_id) WHERE fn.forum_id='" . (int)$getData['fid'] . "' AND fn.notify_status='0' AND (fn.user_id>'" . (int)$this->customer->getForumUserId() . "' OR fn.user_id<'" . (int)$this->customer->getForumUserId() . "')");

        $forumInfo=$this->getForumInfo($getData);

        foreach($query->rows as $result){
            if(isset($result['user_id'])){
                //send mails
                $this->language->load('community/post');

                $subject = sprintf($this->language->get('text_forum_topic_subject'), $forumInfo['forum_name']);

                $message = sprintf($this->language->get('text_forum_topic_content'), $forumInfo['forum_name'], $this->config->get('config_name'), $topicTitle) . "\n\n";
                $message .= $this->language->get('text_forum_topic_view_topic') . "\n";
                $message .=$this->url->link('community/viewtopic', 'fid=' . $getData['fid'] . '&tid=' . $topicId, 'SSL') . "\n\n";
                $message .= $this->language->get('text_forum_topic_view_forum') . "\n";
                $message .=$this->url->link('community/viewforum', 'fid=' . $getData['fid'], 'SSL') . "\n\n";
                $message .= $this->language->get('text_forum_topic_thanks') . "\n";
                $message .= $this->config->get('config_name') . "\40" . $this->language->get('text_forum_topic_team');

                $mail = new Mail();
                $mail->protocol = $this->config->get('config_mail_protocol');
                $mail->parameter = $this->config->get('config_mail_parameter');
                $mail->hostname = $this->config->get('config_smtp_host');
                $mail->username = $this->config->get('config_smtp_username');
                $mail->password = $this->config->get('config_smtp_password');
                $mail->port = $this->config->get('config_smtp_port');
                $mail->timeout = $this->config->get('config_smtp_timeout');
                $mail->setTo($result['user_email']);
                $mail->setFrom($this->config->get('config_community_email'));//to-do: ad communit_email in the admin end
                $mail->setSender($this->config->get('config_name') . "\40" . $this->language->get('text_forum_topic_team'));
                $mail->setSubject(html_entity_decode($subject, ENT_QUOTES, 'UTF-8'));
                $mail->setText(html_entity_decode($message, ENT_QUOTES, 'UTF-8'));
                $mail->send();

                // update community_forum_notify record
                $this->db->query("UPDATE " . DB_PREFIX . "community_forum_notify SET notify_status='1' WHERE forum_id='" . (int)$getData['fid'] . "' AND user_id='" . (int)$result['user_id'] . "'");
            }
        }
    }

    private function sendForumNewPostNotification($getData,$postId){
        $query=$this->db->query("SELECT fn.user_id, u.user_email FROM " . DB_PREFIX . "community_forum_notify AS fn LEFT JOIN " . DB_PREFIX . "community_users AS u ON (fn.user_id=u.user_id) WHERE fn.forum_id='" . (int)$getData['fid'] . "' AND fn.notify_status='0' AND (fn.user_id>'" . (int)$this->customer->getForumUserId() . "' OR fn.user_id<'" . (int)$this->customer->getForumUserId() . "')");

        $topic_title_query=$this->db->query("SELECT topic_title FROM " . DB_PREFIX . "community_topics WHERE topic_id='" . $getData['tid'] . "' LIMIT 0,1");

        $topicTitle=$topic_title_query->row['topic_title'];
        $forumInfo=$this->getForumInfo($getData);

        foreach($query->rows as $result){
            if(isset($result['user_id'])){
                //send mails
                $this->language->load('community/post');

                $subject = sprintf($this->language->get('text_forum_post_subject'), $forumInfo['forum_name']);

                $message = sprintf($this->language->get('text_forum_post_content'), $forumInfo['forum_name'], $this->config->get('config_name'), $topicTitle) . "\n\n";
                $message .= $this->language->get('text_forum_topic_view_post') . "\n";
                $message .= $this->url->link('community/viewtopic', 'fid=' . $getData['fid'] . '&tid=' . $getData['tid'] . '&pid=' . $postId . '#' . $postId, 'SSL') . "\n\n";
                $message .= $this->language->get('text_forum_post_view_topic') . "\n";
                $message .= $this->url->link('community/viewtopic', 'fid=' . $getData['fid'] . '&tid=' . $getData['tid'], 'SSL') . "\n\n";
                $message .= $this->language->get('text_forum_topic_view_forum') . "\n";
                $message .=$this->url->link('community/viewforum', 'fid=' . $getData['fid'] , 'SSL') . "\n\n";
                $message .= $this->language->get('text_forum_topic_thanks') . "\n";
                $message .= $this->config->get('config_name') . "\40" . $this->language->get('text_forum_topic_team');

                $mail = new Mail();
                $mail->protocol = $this->config->get('config_mail_protocol');
                $mail->parameter = $this->config->get('config_mail_parameter');
                $mail->hostname = $this->config->get('config_smtp_host');
                $mail->username = $this->config->get('config_smtp_username');
                $mail->password = $this->config->get('config_smtp_password');
                $mail->port = $this->config->get('config_smtp_port');
                $mail->timeout = $this->config->get('config_smtp_timeout');
                $mail->setTo($result['user_email']);
                $mail->setFrom($this->config->get('config_community_email'));//to-do: ad communit_email in the admin end
                $mail->setSender($this->config->get('config_name') . "\40" . $this->language->get('text_forum_topic_team'));
                $mail->setSubject(html_entity_decode($subject, ENT_QUOTES, 'UTF-8'));
                $mail->setText(html_entity_decode($message, ENT_QUOTES, 'UTF-8'));
                $mail->send();

                // update community_forum_notify record
                $this->db->query("UPDATE " . DB_PREFIX . "community_forum_notify SET notify_status='1' WHERE forum_id='" . (int)$getData['fid'] . "' AND user_id='" . (int)$result['user_id'] . "'");
            }
        }
    }

    private function UpdateUserRank(){
        $query=$this->db->query("SELECT rank_min FROM " . DB_PREFIX . "community_ranks WHERE rank_id>'" . (int)$this->customer->getForumUserRank() . "' LIMIT 0,1");
        if(!empty($query->row) && $this->customer->getForumUserType()==5 && $this->customer->getForumUserPosts()>=$query->row['rank_min']){
            $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_rank='" . (int)$query->row['rank_id'] . "' WHERE user_id='" . (int)$this->customer->getForumUserId() . "'");
        }
    }

    public function loadQuotedContent($getData){
        $query=$this->db->query("SELECT post_text FROM " . DB_PREFIX . "community_posts WHERE post_id='" . (int)$getData['qpid'] . "' LIMIT 0,1");

        return $query->row['post_text'];
    }

    public function addForumPoints(){
        $this->db->query("INSERT INTO " . DB_PREFIX . "community_forum_point_history SET user_id='" . (int)$this->customer->getForumUserId() . "', type='2', description='" . $this->db->escape('Posting') . "', points='" . (int)$this->config->get('posting_awarded_forum_points') . "', add_time='" . time() . "'");

        $this->db->query("UPDATE " . DB_PREFIX . "community_users SET forum_points=forum_points+" . (int)$this->config->get('posting_awarded_forum_points') . " WHERE user_id='" . (int)$this->customer->getForumUserId() . "'");
    }

    public function getLatestPostingForumPointAwardingTime(){
        $query=$this->db->query("SELECT add_time FROM " . DB_PREFIX . "community_forum_point_history WHERE user_id='" . (int)$this->customer->getForumUserId() . "' AND type='2' ORDER BY add_time DESC LIMIT 0," . $this->config->get('daily_awarded_posting_count'));

        return $query->rows;
    }
}
?>